-- **************************************************************************
--    Project Name:   未签收运单概要表宽表-中间表
--    Job Name:       jms_dwd.dwd_wide_summary_waybill
--    Description :   根据运单-操作数据计算未签收运单全链路数据
--    Author :        荣光凡
--    date：          2022/4/7
-- **************************************************************************
--    Relation ：
--    jms_dwd.dwd_wide_summary_waybill << [
--        jms_dwd.dwd_wide_rank_basic_scaninfo_tms_dt,
--        jms_dwd.dwd_yl_oms_oms_waybill_incre_dt,
--        jms_dwd.dwd_wide_abnormal_detail_waybill_dt
--    ]
-- **************************************************************************
--    modify by 荣光凡 2022/4/7 新增脚本
--    modify by 荣光凡 2022/5/5 更改插入表名
--    modify by 荣光凡 2022/5/10 更换dwd_s03_whole_operations_trace_rank_tms_dt为dwd_wide_rank_basic_scaninfo_tms_dt表
--                              增加客户编码与名称、派件网点编码与名称字段
--    modify by 荣光凡 2022/5/16 新增问题件标识
--    modify by 荣光凡 2022/5/20 新增收派签的最后一次扫描网点
-- **************************************************************************


-- DESC：获取操作scaninfo_tms近30天及2049分区数据
with operations_trace_basic as (
select
    waybill_no
    ,step
    ,scan_type
    ,network_code
    ,network_type
    ,network_name
    ,network_scan_time_early
    ,scan_time
    ,step_reserve
    ,step_center
    ,step_center_reserve
    ,step_entrepot
    ,step_entrepot_reserve
    ,is_sign
    ,weight
    ,named_struct(
    'scan_time', scan_time,
    'network_code', network_code,
    'network_type', network_type,
    'network_name', network_name,
    'prev_network_code', prev_network_code,
    'prev_network_type', prev_network_type,
    'next_network_code', next_network_code,
    'next_network_type', next_network_type,
    'scan_type', scan_type,
    'packagecode', packagecode,
    'nextstationcode', nextstationcode,
    'nextstation', nextstation,
    'scanusercode', scanusercode,
    'scanuser', scanuser,
    'weight', weight,
    'shipment_no', shipment_no,
    'planned_arrival_time', planned_arrival_time,
    'actual_arrival_time', actual_arrival_time,
    'planned_departure_time', planned_departure_time,
    'actual_departure_time', actual_departure_time,
    'table_type', table_type,
    'vehicleline_code', vehicleline_code,
    'vehicleline_name', vehicleline_name,
    'subbillcode', subbillcode,
    'pistolid', pistolid
    ) as trace_wid
    ,dt
from jms_dwd.dwd_wide_rank_basic_scaninfo_tms_dt
where dt between date_add('{{ execution_date | cst_ds }}', -29)
and '{{ execution_date | cst_ds }}'

union all

select
    waybill_no
    ,step
    ,scan_type
    ,network_code
    ,network_type
    ,network_name
    ,network_scan_time_early
    ,scan_time
    ,step_reserve
    ,step_center
    ,step_center_reserve
    ,step_entrepot
    ,step_entrepot_reserve
    ,is_sign
    ,weight
    ,named_struct(
    'scan_time', scan_time,
    'network_code', network_code,
    'network_type', network_type,
    'network_name', network_name,
    'prev_network_code', prev_network_code,
    'prev_network_type', prev_network_type,
    'next_network_code', next_network_code,
    'next_network_type', next_network_type,
    'scan_type', scan_type,
    'packagecode', packagecode,
    'nextstationcode', nextstationcode,
    'nextstation', nextstation,
    'scanusercode', scanusercode,
    'scanuser', scanuser,
    'weight', weight,
    'shipment_no', shipment_no,
    'planned_arrival_time', planned_arrival_time,
    'actual_arrival_time', actual_arrival_time,
    'planned_departure_time', planned_departure_time,
    'actual_departure_time', actual_departure_time,
    'table_type', table_type,
    'vehicleline_code', vehicleline_code,
    'vehicleline_name', vehicleline_name,
    'subbillcode', subbillcode,
    'pistolid', pistolid
    ) as trace_wid
    ,dt
from jms_dwd.dwd_wide_rank_basic_scaninfo_tms_dt
where dt = '2049-12-31'
) ,

-- DESC：运单列转行
tmp_waybill_scan as (
    select
        waybill_no
        --最晚揽收时间
        --最早揽收时间
        --最早揽收网点
        --最大揽收重量
        ,max(if(scan_type = 'taking' , scan_time ,null)) as taking_later_scantime
        ,min(if(scan_type = 'taking' , scan_time ,null)) as taking_early_scantime
        ,min(if(scan_type = 'taking' , trace_wid ,null)).network_code as taking_early_scansitecode
        ,cast(max(if(scan_type = 'taking' , weight ,null)) as double) as taking_max_weight
         --最晚入仓时间
         --最早入仓时间
         --最早入仓网点
         --最大入仓重量
        ,max(if(scan_type = 'warehousing' , scan_time ,null)) as warehousing_later_scantime
        ,min(if(scan_type = 'warehousing' , scan_time ,null)) as warehousing_early_scantime
        ,min(if(scan_type = 'warehousing' , trace_wid ,null)).network_code as warehousing_early_scansitecode
        ,cast(max(if(scan_type = 'warehousing' , weight ,null)) as double) as warehousing_max_weight
        --始发网点网点编码
        --始发网点最小重量
        --始发网点最大重量
        --始发网点最晚装车时间
        --始发网点最晚发车时间
        ,min(if(step = 1,network_code,null)) as first_network_network_code
        ,cast(min(if(step = 1 and (scan_type = 'loading' or scan_type = 'unloading' or  scan_type = 'siteSend' or scan_type = 'siteArrival'),weight,null)) as double) as first_network_min_weight
        ,cast(max(if(step = 1 and (scan_type = 'loading' or scan_type = 'unloading' or  scan_type = 'siteSend' or scan_type = 'siteArrival'),weight,null)) as double) as first_network_max_weight
        ,max(if(step = 1 and scan_type = 'loading', scan_time,null))  as first_network_loading_later_time
        ,max(if(step = 1 and (scan_type = 'siteSend'), scan_time, null))  as first_network_send_later_time
        -- 始发网点最早卸车到件扫描对应任务号
        -- 始发网点最早卸车到件规划发车时间
        -- 始发网点最早卸车到件规划到车时间
        -- 始发网点最早卸车到件实际发车时间
        -- 始发网点最早卸车到件实际到车时间
        ,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).shipment_no,max(if(step = 1 and (scan_type = 'siteSend'), trace_wid, null)).shipment_no) as first_network_loading_send_shipment_no
        ,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).planned_departure_time,max(if(step = 1 and (scan_type = 'siteSend'), trace_wid, null)).planned_departure_time)  as first_network_loading_send_planned_departure_time
        ,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).planned_arrival_time,max(if(step = 1 and (scan_type = 'siteSend'), trace_wid, null)).planned_arrival_time)  as first_network_loading_send_planned_arrival_time
        ,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).actual_departure_time,max(if(step = 1 and (scan_type = 'siteSend'), trace_wid, null)).actual_departure_time) as first_network_loading_send_actual_departure_time
        ,coalesce(max(if(step = 1 and  scan_type = 'loading', trace_wid, null)).actual_arrival_time,max(if(step = 1 and (scan_type = 'siteSend'), trace_wid, null)).actual_arrival_time) as first_network_loading_send_actual_arrival_time
        -- 始发网点最晚装车发件下一站网点编码
        -- 始发网点最晚装车发件下一站网点类型
        ,max(if(step = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid, null)).next_network_code  as first_network_loading_send_next_network_code
        ,cast(max(if(step = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid, null)).next_network_type as tinyint)  as first_network_loading_send_next_network_type
        --最早装袋时间
        --最早装袋网点编码
        --最早装袋包号
        ,min(if(scan_type = 'bagging' , scan_time ,null)) as bagging_early_scantime
        ,min(if(scan_type = 'bagging' , trace_wid ,null)).network_code as bagging_early_scansitecode
        ,min(if(scan_type = 'bagging' , trace_wid ,null)).packagecode as bagging_early_package_code
        -- 始发集散网点
        -- 始发集散最小重量
        -- 始发集散最大重量
        ,min(if(step_entrepot = 1 ,network_code,null))  as first_nodal_network_code
        ,cast(min(if(step_entrepot = 1 ,weight,null)) as double) as first_nodal_min_weight
        ,cast(max(if(step_entrepot = 1 ,weight,null)) as double) as first_nodal_max_weight
        -- 始发集散最早到件时间
        -- 始发集散最早卸车时间
        ,min(if(step_entrepot = 1 and scan_type = 'siteArrival', scan_time,null))   as first_nodal_arrival_early_time
        ,min(if(step_entrepot = 1 and scan_type = 'unloading', scan_time,null))   as first_nodal_unloading_early_time
         -- 始发集散最早卸车到件扫描对应任务号
         -- 始发集散最早卸车到件规划发车时间
         -- 始发集散最早卸车到件规划到车时间
         -- 始发集散最早卸车到件实际发车时间
         -- 始发集散最早卸车到件实际到车时间
        ,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).shipment_no) as first_nodal_unloading_arrival_shipment_no
        ,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).planned_departure_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_departure_time) as first_nodal_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).planned_arrival_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_arrival_time) as first_nodal_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).actual_departure_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_departure_time) as first_nodal_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_entrepot = 1 and scan_type = 'unloading', trace_wid,null)).actual_arrival_time,min(if(step_entrepot = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_arrival_time) as first_nodal_unloading_arrival_actual_arrival_time
        --始发集散最晚装车时间
        --始发集散最晚发车时间
        ,max(if(step_entrepot = 1 and scan_type = 'loading', scan_time,null)) as first_nodal_loading_later_time
        ,max(if(step_entrepot = 1 and scan_type = 'siteSend',scan_time,null)) as first_nodal_send_later_time
         -- 始发集散最晚装车发件扫描对应任务号
         -- 始发集散最晚装车发件规划发车时间
         -- 始发集散最晚装车发件规划到车时间
         -- 始发集散最晚装车发件实际发车时间
         -- 始发顺序第一集散最晚装车发件实际到车时间
        ,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).shipment_no) as first_nodal_loading_send_shipment_no
        ,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).planned_departure_time) as first_nodal_loading_send_planned_departure_time
        ,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).planned_arrival_time) as first_nodal_loading_send_planned_arrival_time
        ,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).actual_departure_time) as first_nodal_loading_send_actual_departure_time
        ,coalesce(max(if(step_entrepot = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time, max(if(step_entrepot = 1 and scan_type = 'siteSend', trace_wid,null)).actual_arrival_time) as first_nodal_loading_send_actual_arrival_time
        -- 始发集散最晚装车发件下一站网点编码
        -- 始发集散最晚装车发件下一站网点类型
        ,max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_code as first_nodal_loading_send_next_network_code
        ,cast(max(if(step_entrepot = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_type as tinyint) as first_nodal_loading_send_next_network_type
        -- 始发转运中心
        -- 始发转运中心最小重量
        -- 始发转运中心最大重量
        -- 始发中心最早卸车时间
        -- 始发中心最早到件时间
        ,max(if(step_center = 1, network_code,null)) as first_center_network_code
        ,min(if(step_center = 1, weight,null)) as first_center_min_weight
        ,max(if(step_center = 1, weight,null)) as first_center_max_weight
        ,min(if(step_center = 1 and scan_type = 'unloading', scan_time,null))  as first_center_unloading_early_time
        ,min(if(step_center = 1 and scan_type = 'centerArrival', scan_time,null)) as first_center_arrival_early_time
        -- 顺序第一中心最早卸车到件扫描对应任务号
        -- 顺序第一中心最早卸车到件规划发车时间
        -- 顺序第一中心最早卸车到件规划到车时间
        -- 顺序第一中心最早卸车到件实际发车时间
        -- 顺序第一中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as first_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as first_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as first_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as first_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center = 1 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as first_center_unloading_arrival_actual_arrival_time
        -- 始发中心最晚装车时间
        -- 始发中心最晚发件时间
        ,max(if(step_center = 1 and scan_type = 'loading', scan_time,null)) as first_center_loading_later_time
        ,max(if(step_center = 1 and scan_type = 'centerSend', scan_time,null)) as first_center_send_later_time
        -- 顺序第一中心最晚装车发件扫描对应任务号
        -- 顺序第一中心最晚装车发件规划发车时间
        -- 顺序第一中心最晚装车发件规划到车时间
        -- 顺序第一中心最晚装车发件实际发车时间
        -- 顺序第一中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as first_center_loading_send_shipment_no
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as first_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as first_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as first_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as first_center_loading_send_actual_arrival_time
        -- 始发中心最晚装车发件下一站网点编码
        -- 始发中心最晚装车发件下一站网点类型
        ,max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as first_center_loading_send_next_network_code
        ,cast(max(if(step_center = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as first_center_loading_send_next_network_type
        -- 第二转运中心编码
        -- 第二转运中心最小重量
        -- 第二转运中心最大重量
        -- 第二中心最早卸车时间
        -- 第二中心最早到件时间
        ,max(if(step_center = 2, network_code,null)) as  second_center_network_code
        ,min(if(step_center = 2, weight,null)) as  second_center_min_weight
        ,max(if(step_center = 2, weight,null)) as  second_center_max_weight
        ,min(if(step_center = 2 and scan_type = 'unloading', scan_time,null))  as second_center_unloading_early_time
        ,min(if(step_center = 2 and scan_type = 'centerArrival', scan_time,null)) as second_center_arrival_early_time
        -- 第二中心最早卸车到件扫描对应任务号
        -- 第二中心最早卸车到件规划发车时间
        -- 第二中心最早卸车到件规划到车时间
        -- 第二中心最早卸车到件实际发车时间
        -- 第二中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as second_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as second_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as second_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as second_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center = 2 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 2 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as second_center_unloading_arrival_actual_arrival_time
        -- 第二中心最晚装车时间
        -- 第二中心最晚发件时间
        ,max(if(step_center = 2 and scan_type = 'loading', scan_time,null)) as second_center_loading_later_time
        ,max(if(step_center = 2 and scan_type = 'centerSend' , scan_time,null)) as second_center_send_later_time
        -- 第二中心最晚装车发件扫描对应任务号
        -- 第二中心最晚装车发件规划发车时间
        -- 第二中心最晚装车发件规划到车时间
        -- 第二中心最晚装车发件实际发车时间
        -- 第二中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as second_center_loading_send_shipment_no
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as second_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as second_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as second_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as second_center_loading_send_actual_arrival_time
        -- 第二中心最晚装车发件下一站网点编码
        -- 第二中心最晚装车发件下一站网点类型
        ,max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as second_center_loading_send_next_network_code
        ,cast(max(if(step_center = 2 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as second_center_loading_send_next_network_type
        -- 第三转运中心编码
        -- 第三转运中心最小重量
        -- 第三转运中心最大重量
        -- 第三中心最早卸车时间
        -- 第三中心最早到件时间
        ,max(if(step_center = 3, network_code,null)) as third_center_network_code
        ,cast(min(if(step_center = 3, weight,null)) as double) as third_center_min_weight
        ,cast(max(if(step_center = 3, weight,null)) as double) as third_center_max_weight
        ,min(if(step_center = 3 and scan_type = 'unloading', scan_time,null))  as third_center_unloading_early_time
        ,min(if(step_center = 3 and scan_type = 'centerArrival', scan_time,null)) as third_center_arrival_early_time
        -- 第三中心最早卸车到件扫描对应任务号
        -- 第三中心最早卸车到件规划发车时间
        -- 第三中心最早卸车到件规划到车时间
        -- 第三中心最早卸车到件实际发车时间
        -- 第三中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as third_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as third_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as third_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as third_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center = 3 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 3 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as third_center_unloading_arrival_actual_arrival_time
        -- 第三中心最晚装车时间
        -- 第三中心最晚发件时间
        ,max(if(step_center = 3 and scan_type = 'loading', scan_time,null)) as third_center_loading_later_time
        ,max(if(step_center = 3 and scan_type = 'centerSend', scan_time,null)) as third_center_send_later_time
        -- 第三中心最晚装车发件扫描对应任务号
        -- 第三中心最晚装车发件规划发车时间
        -- 第三中心最晚装车发件规划到车时间
        -- 第三中心最晚装车发件实际发车时间
        -- 第三中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as third_center_loading_send_shipment_no
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as third_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as third_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as third_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as third_center_loading_send_actual_arrival_time
        -- 第三中心最晚装车发件下一站网点编码
        -- 第三中心最晚装车发件下一站网点类型
        ,max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as third_center_loading_send_next_network_code
        ,cast(max(if(step_center = 3 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as third_center_loading_send_next_network_type
        -- 第四转运中心编码
        -- 第四转运中心最小重量
        -- 第四转运中心最大重量
        -- 第四中心最早卸车时间
        -- 第四中心最早到件时间
        ,max(if(step_center = 4, network_code,null)) as fourth_center_network_code
        ,min(if(step_center = 4, weight,null)) as fourth_center_min_weight
        ,max(if(step_center = 4, weight,null)) as fourth_center_max_weight
        ,min(if(step_center = 4 and scan_type = 'unloading', scan_time,null))  as fourth_center_unloading_early_time
        ,min(if(step_center = 4 and scan_type = 'centerArrival', scan_time,null)) as fourth_center_arrival_early_time
        -- 第四中心最早卸车到件扫描对应任务号
        -- 第四中心最早卸车到件规划发车时间
        -- 第四中心最早卸车到件规划到车时间
        -- 第四中心最早卸车到件实际发车时间
        -- 第四中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as fourth_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as fourth_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as fourth_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as fourth_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center = 4 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 4 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as fourth_center_unloading_arrival_actual_arrival_time
         -- 第四中心最晚装车时间
         -- 第四中心最晚发件时间
        ,max(if(step_center = 4 and scan_type = 'loading', scan_time,null)) as fourth_center_loading_later_time
        ,max(if(step_center = 4 and scan_type = 'centerSend', scan_time,null)) as fourth_center_send_later_time
         -- 第四中心最晚装车发件扫描对应任务号
         -- 第四中心最晚装车发件规划发车时间
         -- 第四中心最晚装车发件规划到车时间
         -- 第四中心最晚装车发件实际发车时间
         -- 第四中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as fourth_center_loading_send_shipment_no
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as fourth_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as fourth_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as fourth_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as fourth_center_loading_send_actual_arrival_time
        -- 第四中心最晚装车发件下一站网点编码
        -- 第四中心最晚装车发件下一站网点类型
        ,max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as fourth_center_loading_send_next_network_code
        ,cast(max(if(step_center = 4 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as fourth_center_loading_send_next_network_type
        -- 第五转运中心编码
        -- 第五转运中心最小重量
        -- 第五转运中心最大重量
        -- 第五中心最早卸车时间
        -- 第五中心最早到件时间
        ,max(if(step_center = 5, network_code,null)) as fifth_center_network_code
        ,min(if(step_center = 5, weight,null)) as fifth_center_min_weight
        ,max(if(step_center = 5, weight,null)) as fifth_center_max_weight
        ,min(if(step_center = 5 and scan_type = 'unloading', scan_time,null))  as fifth_center_unloading_early_time
        ,min(if(step_center = 5 and scan_type = 'centerArrival', scan_time,null)) as fifth_center_arrival_early_time
        -- 第五中心最早卸车到件扫描对应任务号
        -- 第五中心最早卸车到件规划发车时间
        -- 第五中心最早卸车到件规划到车时间
        -- 第五中心最早卸车到件实际发车时间
        -- 第五中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center = 5 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center = 5 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as fifth_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center = 5 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center = 5 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as fifth_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center = 5 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center = 5 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as fifth_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center = 5 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center = 5 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as fifth_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center = 5 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center = 5 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as fifth_center_unloading_arrival_actual_arrival_time
        -- 第五中心最晚装车时间
        -- 第五中心最晚发件时间
        ,max(if(step_center = 5 and scan_type = 'loading', scan_time,null)) as fifth_center_loading_later_time
        ,max(if(step_center = 5 and scan_type = 'centerSend', scan_time,null)) as fifth_center_send_later_time
        -- 第五中心最晚装车发件扫描对应任务号
        -- 第五中心最晚装车发件规划发车时间
        -- 第五中心最晚装车发件规划到车时间
        -- 第五中心最晚装车发件实际发车时间
        -- 第五中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as fifth_center_loading_send_shipment_no
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as fifth_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as fifth_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as fifth_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as fifth_center_loading_send_actual_arrival_time
        -- 第五中心最晚装车发件下一站网点编码
        -- 第五中心最晚装车发件下一站网点类型
        ,max(if(step_center = 5 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as fifth_center_loading_send_next_network_code
        ,cast(max(if(step_center = 5 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as fifth_center_loading_send_next_network_type
        -- 末端转运中心编码
        -- 末端转运中心最小重量
        -- 末端转运中心最大重量
        -- 末端中心最早卸车时间
        -- 末端中心最早到件时间
        ,max(if(step_center_reserve = 1, network_code,null)) as  end_center_network_code
        ,min(if(step_center_reserve = 1, weight,null)) as  end_center_min_weight
        ,max(if(step_center_reserve = 1, weight,null)) as  end_center_max_weight
        ,min(if(step_center_reserve = 1 and scan_type = 'unloading', scan_time,null))  as end_center_unloading_early_time
        ,min(if(step_center_reserve = 1 and scan_type = 'centerArrival', scan_time,null)) as end_center_arrival_early_time
        -- 末端中心最早卸车到件扫描对应任务号
        -- 末端中心最早卸车到件规划发车时间
        -- 末端中心最早卸车到件规划到车时间
        -- 末端中心最早卸车到件实际发车时间
        -- 末端中心最早卸车到件实际到车时间
        ,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).shipment_no) as end_center_unloading_arrival_shipment_no
        ,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).planned_departure_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_departure_time) as end_center_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).planned_arrival_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).planned_arrival_time) as end_center_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).actual_departure_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_departure_time) as end_center_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_center_reserve = 1 and  scan_type = 'unloading', trace_wid,null)).actual_arrival_time, min(if(step_center_reserve = 1 and scan_type = 'centerArrival', trace_wid,null)).actual_arrival_time) as end_center_unloading_arrival_actual_arrival_time
        --末端中心最晚装车时间
        --末端中心最晚发件时间
        ,max(if(step_center_reserve = 1 and scan_type = 'loading', scan_time,null)) as end_center_loading_later_time
        ,max(if(step_center_reserve = 1 and scan_type = 'centerSend', scan_time,null)) as end_center_send_later_time
        -- 末端中心最晚装车发件扫描对应任务号
        -- 末端中心最晚装车发件规划发车时间
        -- 末端中心最晚装车发件规划到车时间
        -- 末端中心最晚装车发件实际发车时间
        -- 末端中心最晚装车发件实际到车时间
        ,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).shipment_no) as end_center_loading_send_shipment_no
        ,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).planned_departure_time) as end_center_loading_send_planned_departure_time
        ,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).planned_arrival_time) as end_center_loading_send_planned_arrival_time
        ,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).actual_departure_time) as end_center_loading_send_actual_departure_time
        ,coalesce(max(if(step_center_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time,max(if(step_center_reserve = 1 and scan_type = 'centerSend', trace_wid,null)).actual_arrival_time) as end_center_loading_send_actual_arrival_time
        -- 末端中心最晚装车发件下一站网点编码
        -- 末端中心最晚装车发件下一站网点类型
        ,max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_code as end_center_loading_send_next_network_code
        ,cast(max(if(step_center_reserve = 1 and (scan_type = 'loading' or scan_type = 'centerSend'), trace_wid,null)).next_network_type as tinyint) as end_center_loading_send_next_network_type
        -- 末端集散网点编码
        -- 末端集散网点最小重量
        -- 末端集散网点最大重量
        -- 末端集散网点最早卸车时间
        -- 末端集散网点最早到件时间
        ,max(if(step_entrepot_reserve = 1, network_code,null)) as end_nodal_network_code
        ,min(if(step_entrepot_reserve = 1, weight,null)) as end_nodal_min_weight
        ,max(if(step_entrepot_reserve = 1, weight,null)) as end_nodal_max_weight
        ,min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', scan_time,null))   as end_nodal_unloading_early_time
        ,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', scan_time,null))  as end_nodal_arrival_early_time
        -- 末端集散最早卸车到件扫描对应任务号
        -- 末端集散最早卸车到件规划发车时间
        -- 末端集散最早卸车到件规划到车时间
        -- 末端集散最早卸车到件实际发车时间
        -- 末端集散最早卸车到件实际到车时间
        ,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).shipment_no,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).shipment_no) as end_nodal_unloading_arrival_shipment_no
        ,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).planned_departure_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_departure_time) as end_nodal_unloading_arrival_planned_departure_time
        ,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).planned_arrival_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).planned_arrival_time) as end_nodal_unloading_arrival_planned_arrival_time
        ,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).actual_departure_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_departure_time) as end_nodal_unloading_arrival_actual_departure_time
        ,coalesce(min(if(step_entrepot_reserve = 1 and scan_type = 'unloading', trace_wid,null)).actual_arrival_time,min(if(step_entrepot_reserve = 1 and scan_type = 'siteArrival', trace_wid,null)).actual_arrival_time) as end_nodal_unloading_arrival_actual_arrival_time
        -- 末端集散最晚装车时间
        -- 末端集散最晚发车时间
        ,max(if(step_entrepot_reserve = 1 and scan_type = 'loading', scan_time,null))   as end_nodal_loading_later_time
        ,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend' , scan_time,null))   as end_nodal_send_later_time
        -- 末端集散最晚装车发件扫描对应任务号
        -- 末端集散最晚装车发件规划发车时间
        -- 末端集散最晚装车发件规划到车时间
        -- 末端集散最晚装车发件实际发车时间
        -- 末端集散最晚装车发件实际到车时间
        ,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).shipment_no,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).shipment_no) as end_nodal_loading_send_shipment_no
        ,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_departure_time,max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).planned_departure_time) as end_nodal_loading_send_planned_departure_time
        ,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).planned_arrival_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).planned_arrival_time) as end_nodal_loading_send_planned_arrival_time
        ,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_departure_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).actual_departure_time) as end_nodal_loading_send_actual_departure_time
        ,coalesce(max(if(step_entrepot_reserve = 1 and scan_type = 'loading', trace_wid,null)).actual_arrival_time, max(if(step_entrepot_reserve = 1 and scan_type = 'siteSend', trace_wid,null)).actual_arrival_time) as end_nodal_loading_send_actual_arrival_time
        --末端集散最晚装车发件下一站网点编码
        --末端集散最晚装车发件下一站网点类型
        ,max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_code as end_nodal_loading_send_next_network_code
        ,cast(max(if(step_entrepot_reserve = 1 and (scan_type = 'loading' or scan_type = 'siteSend'), trace_wid,null)).next_network_type as tinyint) as end_nodal_loading_send_next_network_type
        -- 末端网点编码
        -- 末端网点最小重量
        -- 末端网点最大重量
        -- 末端网点最早卸车时间
        -- 末端网点最早到件时间
        ,max(trace_wid).network_code as end_network_network_code
        ,min(if(step_reserve = 1,weight, null))  as end_network_min_weight
        ,max(if(step_reserve = 1,weight, null))  as end_network_max_weight
        ,min(if(scan_type = 'unloading' and step_reserve = 1, scan_time,null))    as end_network_unloading_early_time
        ,min(if(scan_type = 'siteArrival' and step_reserve = 1, scan_time,null))  as end_network_arrival_early_time
        -- 末端网点最早卸车到件扫描对应任务号
        -- 末端网点最早卸车到件规划发车时间
        -- 末端网点最早卸车到件规划到车时间
        -- 末端网点最早卸车到件实际发车时间
        -- 末端网点最早卸车到件实际到车时间
        ,coalesce(max(if(scan_type = 'unloading', trace_wid,null)).shipment_no,max(if(scan_type = 'siteArrival', trace_wid,null)).shipment_no) as end_network_unloading_arrival_shipment_no
        ,coalesce(max(if(scan_type = 'unloading', trace_wid,null)).planned_departure_time,max(if(scan_type = 'siteArrival', trace_wid,null)).planned_departure_time) as end_network_unloading_arrival_planned_departure_time
        ,coalesce(max(if(scan_type = 'unloading', trace_wid,null)).planned_arrival_time,max(if(scan_type = 'siteArrival', trace_wid,null)).planned_arrival_time) as end_network_unloading_arrival_planned_arrival_time
        ,coalesce(max(if(scan_type = 'unloading', trace_wid,null)).actual_departure_time,max(if(scan_type = 'siteArrival', trace_wid,null)).actual_departure_time) as end_network_unloading_arrival_actual_departure_time
        ,coalesce(max(if(scan_type = 'unloading', trace_wid,null)).actual_arrival_time,max(if(scan_type = 'siteArrival', trace_wid,null)).actual_arrival_time) as end_network_unloading_arrival_actual_arrival_time
        --最早派件时间
        --最晚派件时间
        --最晚派件网点
        --派件最大重量
        --派件人
        ,min(if(scan_type = 'deliver',scan_time,null)) as deliver_early_scantime
        ,max(if(scan_type = 'deliver',scan_time,null)) as deliver_later_scantime
        ,max(if(scan_type = 'deliver',trace_wid,null)).network_code as deliver_later_scansitecode
        ,cast(max(if(scan_type = 'deliver',weight,null)) as double) as deliver_max_weight
        ,max(if(scan_type = 'deliver',trace_wid,null)).scanuser as deliver_later_user
        --第一次入库时间
        --最后一次入库时间
        --第一次入库网点
        --入库最大重量
        ,min(if(scan_type = 'inStore',scan_time,null)) as instore_early_scantime
        ,max(if(scan_type = 'inStore',scan_time,null)) as instore_later_scantime
        ,min(if(scan_type = 'inStore',trace_wid,null)).network_code as instore_early_scansitecode
        ,max(if(scan_type = 'inStore',weight,null)) as instore_max_weight
        --第一次出库时间
        --最后一次出库时间
        --第一次出库网点
        --出库最大重量
        ,min(if(scan_type = 'outStore',scan_time,null)) as outstore_early_scantime
        ,max(if(scan_type = 'outStore',scan_time,null)) as outstore_later_scantime
        ,min(if(scan_type = 'outStore',trace_wid,null)).network_code as outstore_early_scansitecode
        ,max(if(scan_type = 'outStore',weight,null)) as outstore_max_weight
        --第一次签收时间
        --最后一次签收时间
        --第一次签收扫描网点
        --签收最大重量
        ,min(if(scan_type = 'sign',scan_time,null)) as sign_early_scantime
        ,max(if(scan_type = 'sign',scan_time,null)) as sign_later_scantime
        ,min(if(scan_type = 'sign',trace_wid,null)).network_code as sign_early_scansitecode
        ,cast(max(if(scan_type = 'sign' or scan_type = 'outStore',weight,null)) as double) as sign_max_weight
        --最早派件网点
        --最晚揽收网点
        --最晚入仓网点
        --最晚入库网点
        --最晚出库网点
        --最晚签收网点
        ,min(if(scan_type = 'deliver',trace_wid,null)).network_code as deliver_early_scansitecode
        ,max(if(scan_type = 'taking' , trace_wid ,null)).network_code as taking_later_scansitecode
        ,max(if(scan_type = 'warehousing' , trace_wid ,null)).network_code as warehousing_later_scansitecode
        ,max(if(scan_type = 'inStore',trace_wid,null)).network_code as instore_later_scansitecode
        ,max(if(scan_type = 'outStore',trace_wid,null)).network_code as outstore_later_scansitecode
        ,max(if(scan_type = 'sign',trace_wid,null)).network_code as sign_later_scansitecode
        --始发中心最晚装车发件扫描对应任务号类型 1-干线 2-支线
        --第二中心最晚装车发件扫描对应任务号类型 1-干线 2-支线
        --第三中心最晚装车发件扫描对应任务号类型 1-干线 2-支线
        --第四中心最晚装车发件扫描对应任务号类型 1-干线 2-支线
        --第五中心最晚装车发件扫描对应任务号类型 1-干线 2-支线
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).table_type,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).table_type) as first_center_loading_send_shipment_type
        ,coalesce(max(if(step_center = 2 and scan_type = 'loading', trace_wid,null)).table_type,max(if(step_center = 2 and scan_type = 'centerSend', trace_wid,null)).table_type) as second_center_loading_send_shipment_type
        ,coalesce(max(if(step_center = 3 and scan_type = 'loading', trace_wid,null)).table_type,max(if(step_center = 3 and scan_type = 'centerSend', trace_wid,null)).table_type) as third_center_loading_send_shipment_type
        ,coalesce(max(if(step_center = 4 and scan_type = 'loading', trace_wid,null)).table_type,max(if(step_center = 4 and scan_type = 'centerSend', trace_wid,null)).table_type) as fourth_center_loading_send_shipment_type
        ,coalesce(max(if(step_center = 5 and scan_type = 'loading', trace_wid,null)).table_type,max(if(step_center = 5 and scan_type = 'centerSend', trace_wid,null)).table_type) as fifth_center_loading_send_shipment_type
        --顺序第一中心最晚装车发件参考车线编码
        --顺序第一中心最晚装车发件参考车线名称
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).vehicleline_code,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).vehicleline_code) as first_center_loading_send_vehicleline_code
        ,coalesce(max(if(step_center = 1 and scan_type = 'loading', trace_wid,null)).vehicleline_name,max(if(step_center = 1 and scan_type = 'centerSend', trace_wid,null)).vehicleline_name) as first_center_loading_send_vehicleline_name
        --最晚派件员编码
        ,max(if(scan_type = 'deliver',trace_wid,null)).scanusercode as deliver_later_user_code
        --最早扫描时间
        --最早签收时间
        -- 待测试
        -- ,to_date(min(if(substr(network_scan_time_early,1,10) < date_add('{{ execution_date | cst_ds }}', -29),concat(date_add('{{ execution_date | cst_ds }}', 2), ' 00:00:00'),network_scan_time_early))) as unsign_dt
        ,to_date(min(network_scan_time_early)) as unsign_dt
        ,to_date(min(if(scan_type = 'sign' or scan_type = 'outStore',scan_time,null))) as sign_dt
        --第一次入库门店编码
        --最后一次入库门店编码
        --第一次出库门店编码
        --最后一次出库门店编码
        ,min(if(scan_type = 'inStore',trace_wid,null)).subbillcode as instore_early_storecode
        ,max(if(scan_type = 'inStore',trace_wid,null)).subbillcode as instore_later_storecode
        ,min(if(scan_type = 'outStore',trace_wid,null)).subbillcode as outstore_early_storecode
        ,max(if(scan_type = 'outStore',trace_wid,null)).subbillcode as outstore_later_storecode
        --最早派件设备编码
        --最晚派件设备编码
        ,min(if(scan_type = 'deliver',trace_wid,null)).pistolid as deliver_early_pistol_id
        ,max(if(scan_type = 'deliver',trace_wid,null)).pistolid as deliver_later_pistol_id
        --最晚站点最晚到件时间
        ,max(if(scan_type = 'siteArrival' or scan_type = 'centerArrival', scan_time, null))  as end_site_arrival_later_time
        --运单创建时间
        ,min(dt) as dt
    from operations_trace_basic
    group by waybill_no
),
-- DESC：获取运单去重表近30天数据
tmp_waybill_detail as (
    select
        waybill_no -- 运单号
        ,first_code                     --一段码
        ,second_code                    --二段码
        ,third_code                     --三段码
        --四段码
        --面单发放网点
        ,waybill_source_code            --运单来源code
        ,waybill_source_name            --运单来源名称
        ,is_void                        --是否作废件
        ,pick_network_code              --寄件网点code
        ,pick_network_name              --寄件网点名称
        ,input_time                     --运单录入时间
        ,input_network_code             --录入网点code
        ,settlement_weight              --结算重量
        ,order_source_code              --订单来源编码
        ,is_delete                      --是否删除，1未删除，2已删除
        ,ordersource_code               --多平台订单来源编码
        ,ordersource_name               --多平的订单来源名称
        ,waybill_weight                 --运单重量，单位千克
        ,package_collect_weight         --收件重量，单位千克
        ,package_receipt_weight         --入仓重量，单位千克
        ,package_inside_charge_weight   --内部计费重量，单位千克
        --38线
        ,is_sign                        --签收标识
        ,goods_type_id                  --物品类型id
        ,goods_type_code                --物品类型code
        ,goods_type_name                --物品类型名称
        ,goods_name                     --物品名称
        ,is_abnormal                    --问题件标识，1是，0否
        -- abnormal_type                --问题件类型
        -- is_signed_delete             --是否签收后删除
        ,is_refund                      --是否退回件，1是，0否
        ,waybill_status_code            --运单状态码
        ,customer_code
        ,customer_name
        ,dispatch_network_code
        ,dispatch_network_name
        ,receiver_mobile_phone
        ,dispatch_staff_code
        ,dispatch_staff_name
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -29)
    and '{{ execution_date | cst_ds }}'
),
-- DESC：获取问题件宽表近30天数据
tmp_abnormal_detail as (
    select waybill_no
         , is_reback
         , reback_apply_type_code
         , reback_status as reback_apply_status
         , is_difficult
         , is_end_piece
         , is_intercept
         , difficult_type_code
         , end_piece_type_code
         , intercept_type_code
    from jms_dwd.dwd_wide_abnormal_detail_waybill_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -29)
    and '{{ execution_date | cst_ds }}'
),
--
tmp_order_detail as (
select waybill_id
     ,phone_contact
     ,is_privacy
     ,order_source_code
     ,order_source_name
from jms_dwd.dwd_yl_oms_oms_order_dt
where dt between date_add('{{ execution_date | cst_ds }}', -29)
and '{{ execution_date | cst_ds }}'
)
-- DESC：scaninfo_tms数据关联运单去重表、问题件宽表
insert overwrite  table  jms_dwd.dwd_wide_summary_waybill partition(dt)
select
    tmp_scan.waybill_no
    ,tmp_waybill.first_code
    ,tmp_waybill.second_code
    ,tmp_waybill.third_code
    ,'' as four_code
    ,'' as faceorder_pick_network_code
    ,tmp_waybill.waybill_source_code
    ,tmp_waybill.waybill_source_name
    ,tmp_waybill.is_void
    ,tmp_waybill.pick_network_code
    ,tmp_waybill.pick_network_name
    ,tmp_waybill.input_time
    ,tmp_waybill.input_network_code
    ,tmp_waybill.settlement_weight
    ,tmp_waybill.order_source_code
    ,tmp_waybill.is_delete
    ,tmp_waybill.ordersource_code
    ,tmp_waybill.ordersource_name
    ,tmp_waybill.waybill_weight
    ,tmp_waybill.package_collect_weight
    ,tmp_waybill.package_receipt_weight
    ,tmp_waybill.package_inside_charge_weight
    ,tmp_scan.taking_later_scantime
    ,tmp_scan.taking_early_scantime
    ,tmp_scan.taking_early_scansitecode
    ,tmp_scan.taking_max_weight
    ,tmp_scan.warehousing_later_scantime
    ,tmp_scan.warehousing_early_scantime
    ,tmp_scan.warehousing_early_scansitecode
    ,tmp_scan.warehousing_max_weight
    ,tmp_scan.first_network_network_code
    ,tmp_scan.first_network_min_weight
    ,tmp_scan.first_network_max_weight
    ,tmp_scan.first_network_loading_later_time
    ,tmp_scan.first_network_send_later_time
    ,tmp_scan.first_network_loading_send_shipment_no
    ,tmp_scan.first_network_loading_send_planned_departure_time
    ,tmp_scan.first_network_loading_send_planned_arrival_time
    ,tmp_scan.first_network_loading_send_actual_departure_time
    ,tmp_scan.first_network_loading_send_actual_arrival_time
    ,tmp_scan.first_network_loading_send_next_network_code
    ,tmp_scan.first_network_loading_send_next_network_type
    ,tmp_scan.bagging_early_scantime
    ,tmp_scan.bagging_early_scansitecode
    ,tmp_scan.bagging_early_package_code
    ,tmp_scan.first_nodal_network_code
    ,tmp_scan.first_nodal_min_weight
    ,tmp_scan.first_nodal_max_weight
    ,tmp_scan.first_nodal_arrival_early_time
    ,tmp_scan.first_nodal_unloading_early_time
    ,tmp_scan.first_nodal_unloading_arrival_shipment_no
    ,tmp_scan.first_nodal_unloading_arrival_planned_departure_time
    ,tmp_scan.first_nodal_unloading_arrival_planned_arrival_time
    ,tmp_scan.first_nodal_unloading_arrival_actual_departure_time
    ,tmp_scan.first_nodal_unloading_arrival_actual_arrival_time
    ,tmp_scan.first_nodal_loading_later_time
    ,tmp_scan.first_nodal_send_later_time
    ,tmp_scan.first_nodal_loading_send_shipment_no
    ,tmp_scan.first_nodal_loading_send_planned_departure_time
    ,tmp_scan.first_nodal_loading_send_planned_arrival_time
    ,tmp_scan.first_nodal_loading_send_actual_departure_time
    ,tmp_scan.first_nodal_loading_send_actual_arrival_time
    ,tmp_scan.first_nodal_loading_send_next_network_code
    ,tmp_scan.first_nodal_loading_send_next_network_type
    ,tmp_scan.first_center_network_code
    ,tmp_scan.first_center_min_weight
    ,tmp_scan.first_center_max_weight
    ,tmp_scan.first_center_unloading_early_time
    ,tmp_scan.first_center_arrival_early_time
    ,tmp_scan.first_center_unloading_arrival_shipment_no
    ,tmp_scan.first_center_unloading_arrival_planned_departure_time
    ,tmp_scan.first_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.first_center_unloading_arrival_actual_departure_time
    ,tmp_scan.first_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.first_center_loading_later_time
    ,tmp_scan.first_center_send_later_time
    ,tmp_scan.first_center_loading_send_shipment_no
    ,tmp_scan.first_center_loading_send_planned_departure_time
    ,tmp_scan.first_center_loading_send_planned_arrival_time
    ,tmp_scan.first_center_loading_send_actual_departure_time
    ,tmp_scan.first_center_loading_send_actual_arrival_time
    ,tmp_scan.first_center_loading_send_next_network_code
    ,tmp_scan.first_center_loading_send_next_network_type
    ,tmp_scan.second_center_network_code
    ,tmp_scan.second_center_min_weight
    ,tmp_scan.second_center_max_weight
    ,tmp_scan.second_center_unloading_early_time
    ,tmp_scan.second_center_arrival_early_time
    ,tmp_scan.second_center_unloading_arrival_shipment_no
    ,tmp_scan.second_center_unloading_arrival_planned_departure_time
    ,tmp_scan.second_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.second_center_unloading_arrival_actual_departure_time
    ,tmp_scan.second_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.second_center_loading_later_time
    ,tmp_scan.second_center_send_later_time
    ,tmp_scan.second_center_loading_send_shipment_no
    ,tmp_scan.second_center_loading_send_planned_departure_time
    ,tmp_scan.second_center_loading_send_planned_arrival_time
    ,tmp_scan.second_center_loading_send_actual_departure_time
    ,tmp_scan.second_center_loading_send_actual_arrival_time
    ,tmp_scan.second_center_loading_send_next_network_code
    ,tmp_scan.second_center_loading_send_next_network_type
    ,tmp_scan.third_center_network_code
    ,tmp_scan.third_center_min_weight
    ,tmp_scan.third_center_max_weight
    ,tmp_scan.third_center_unloading_early_time
    ,tmp_scan.third_center_arrival_early_time
    ,tmp_scan.third_center_unloading_arrival_shipment_no
    ,tmp_scan.third_center_unloading_arrival_planned_departure_time
    ,tmp_scan.third_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.third_center_unloading_arrival_actual_departure_time
    ,tmp_scan.third_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.third_center_loading_later_time
    ,tmp_scan.third_center_send_later_time
    ,tmp_scan.third_center_loading_send_shipment_no
    ,tmp_scan.third_center_loading_send_planned_departure_time
    ,tmp_scan.third_center_loading_send_planned_arrival_time
    ,tmp_scan.third_center_loading_send_actual_departure_time
    ,tmp_scan.third_center_loading_send_actual_arrival_time
    ,tmp_scan.third_center_loading_send_next_network_code
    ,tmp_scan.third_center_loading_send_next_network_type
    ,tmp_scan.fourth_center_network_code
    ,tmp_scan.fourth_center_min_weight
    ,tmp_scan.fourth_center_max_weight
    ,tmp_scan.fourth_center_unloading_early_time
    ,tmp_scan.fourth_center_arrival_early_time
    ,tmp_scan.fourth_center_unloading_arrival_shipment_no
    ,tmp_scan.fourth_center_unloading_arrival_planned_departure_time
    ,tmp_scan.fourth_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.fourth_center_unloading_arrival_actual_departure_time
    ,tmp_scan.fourth_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.fourth_center_loading_later_time
    ,tmp_scan.fourth_center_send_later_time
    ,tmp_scan.fourth_center_loading_send_shipment_no
    ,tmp_scan.fourth_center_loading_send_planned_departure_time
    ,tmp_scan.fourth_center_loading_send_planned_arrival_time
    ,tmp_scan.fourth_center_loading_send_actual_departure_time
    ,tmp_scan.fourth_center_loading_send_actual_arrival_time
    ,tmp_scan.fourth_center_loading_send_next_network_code
    ,tmp_scan.fourth_center_loading_send_next_network_type
    ,tmp_scan.fifth_center_network_code
    ,tmp_scan.fifth_center_min_weight
    ,tmp_scan.fifth_center_max_weight
    ,tmp_scan.fifth_center_unloading_early_time
    ,tmp_scan.fifth_center_arrival_early_time
    ,tmp_scan.fifth_center_unloading_arrival_shipment_no
    ,tmp_scan.fifth_center_unloading_arrival_planned_departure_time
    ,tmp_scan.fifth_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.fifth_center_unloading_arrival_actual_departure_time
    ,tmp_scan.fifth_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.fifth_center_loading_later_time
    ,tmp_scan.fifth_center_send_later_time
    ,tmp_scan.fifth_center_loading_send_shipment_no
    ,tmp_scan.fifth_center_loading_send_planned_departure_time
    ,tmp_scan.fifth_center_loading_send_planned_arrival_time
    ,tmp_scan.fifth_center_loading_send_actual_departure_time
    ,tmp_scan.fifth_center_loading_send_actual_arrival_time
    ,tmp_scan.fifth_center_loading_send_next_network_code
    ,tmp_scan.fifth_center_loading_send_next_network_type
    ,tmp_scan.end_center_network_code
    ,tmp_scan.end_center_min_weight
    ,tmp_scan.end_center_max_weight
    ,tmp_scan.end_center_unloading_early_time
    ,tmp_scan.end_center_arrival_early_time
    ,tmp_scan.end_center_unloading_arrival_shipment_no
    ,tmp_scan.end_center_unloading_arrival_planned_departure_time
    ,tmp_scan.end_center_unloading_arrival_planned_arrival_time
    ,tmp_scan.end_center_unloading_arrival_actual_departure_time
    ,tmp_scan.end_center_unloading_arrival_actual_arrival_time
    ,tmp_scan.end_center_loading_later_time
    ,tmp_scan.end_center_send_later_time
    ,tmp_scan.end_center_loading_send_shipment_no
    ,tmp_scan.end_center_loading_send_planned_departure_time
    ,tmp_scan.end_center_loading_send_planned_arrival_time
    ,tmp_scan.end_center_loading_send_actual_departure_time
    ,tmp_scan.end_center_loading_send_actual_arrival_time
    ,tmp_scan.end_center_loading_send_next_network_code
    ,tmp_scan.end_center_loading_send_next_network_type
    ,tmp_scan.end_nodal_network_code
    ,tmp_scan.end_nodal_min_weight
    ,tmp_scan.end_nodal_max_weight
    ,tmp_scan.end_nodal_arrival_early_time
    ,tmp_scan.end_nodal_unloading_early_time
    ,tmp_scan.end_nodal_unloading_arrival_shipment_no
    ,tmp_scan.end_nodal_unloading_arrival_planned_departure_time
    ,tmp_scan.end_nodal_unloading_arrival_planned_arrival_time
    ,tmp_scan.end_nodal_unloading_arrival_actual_departure_time
    ,tmp_scan.end_nodal_unloading_arrival_actual_arrival_time
    ,tmp_scan.end_nodal_loading_later_time
    ,tmp_scan.end_nodal_send_later_time
    ,tmp_scan.end_nodal_loading_send_shipment_no
    ,tmp_scan.end_nodal_loading_send_planned_departure_time
    ,tmp_scan.end_nodal_loading_send_planned_arrival_time
    ,tmp_scan.end_nodal_loading_send_actual_departure_time
    ,tmp_scan.end_nodal_loading_send_actual_arrival_time
    ,tmp_scan.end_nodal_loading_send_next_network_code
    ,tmp_scan.end_nodal_loading_send_next_network_type
    ,tmp_scan.end_network_network_code
    ,tmp_scan.end_network_min_weight
    ,tmp_scan.end_network_max_weight
    ,tmp_scan.end_network_arrival_early_time
    ,tmp_scan.end_network_unloading_early_time
    ,tmp_scan.end_network_unloading_arrival_shipment_no
    ,tmp_scan.end_network_unloading_arrival_planned_departure_time
    ,tmp_scan.end_network_unloading_arrival_planned_arrival_time
    ,tmp_scan.end_network_unloading_arrival_actual_departure_time
    ,tmp_scan.end_network_unloading_arrival_actual_arrival_time
    ,tmp_scan.deliver_early_scantime
    ,tmp_scan.deliver_later_scantime
    ,tmp_scan.deliver_later_scansitecode
    ,tmp_scan.deliver_max_weight
    ,tmp_scan.deliver_later_user
    ,tmp_scan.instore_early_scantime
    ,tmp_scan.instore_later_scantime
    ,tmp_scan.instore_early_scansitecode
    ,tmp_scan.instore_max_weight
    ,tmp_scan.outstore_early_scantime
    ,tmp_scan.outstore_later_scantime
    ,tmp_scan.outstore_early_scansitecode
    ,tmp_scan.outstore_max_weight
    ,tmp_scan.sign_early_scantime
    ,tmp_scan.sign_later_scantime
    ,tmp_scan.sign_early_scansitecode
    ,tmp_scan.sign_max_weight
    ,tmp_waybill.is_sign
    ,tmp_waybill.goods_type_id
    ,tmp_waybill.goods_type_code
    ,tmp_waybill.goods_type_name
    ,tmp_waybill.goods_name
    ,tmp_waybill.is_abnormal
    ,'' as abnormal_type
    ,'' as is_signed_delete
    ,tmp_waybill.is_refund
    ,tmp_waybill.customer_code
    ,tmp_waybill.customer_name
    ,tmp_waybill.dispatch_network_code
    ,tmp_waybill.dispatch_network_name
    ,if(tmp_abnormal.is_reback is null,0,tmp_abnormal.is_reback) as is_reback
    ,if(tmp_abnormal.is_difficult is null,0,tmp_abnormal.is_difficult) as is_difficult
    ,if(tmp_abnormal.is_end_piece is null,0,tmp_abnormal.is_end_piece) as is_end_piece
    ,if(tmp_abnormal.is_intercept is null,0,tmp_abnormal.is_intercept) as is_intercept
    ,if(tmp_abnormal.reback_apply_type_code is null,'0',tmp_abnormal.reback_apply_type_code) as reback_apply_type_code
    ,if(tmp_abnormal.reback_apply_status is null,'0',tmp_abnormal.reback_apply_status) as reback_apply_status
    ,tmp_scan.deliver_early_scansitecode
    ,tmp_scan.taking_later_scansitecode
    ,tmp_scan.warehousing_later_scansitecode
    ,tmp_scan.instore_later_scansitecode
    ,tmp_scan.outstore_later_scansitecode
    ,tmp_scan.sign_later_scansitecode
    ,tmp_scan.first_center_loading_send_shipment_type
    ,tmp_scan.second_center_loading_send_shipment_type
    ,tmp_scan.third_center_loading_send_shipment_type
    ,tmp_scan.fourth_center_loading_send_shipment_type
    ,tmp_scan.fifth_center_loading_send_shipment_type
    ,tmp_waybill.receiver_mobile_phone
    ,tmp_waybill.dispatch_staff_code
    ,tmp_waybill.dispatch_staff_name
    ,concat('{{ execution_date | date_add(1) | cst_ds }}',' 00:00:00') as etl_load_time
    ,tmp_scan.first_center_loading_send_vehicleline_code
    ,tmp_scan.first_center_loading_send_vehicleline_name
    ,tmp_scan.deliver_later_user_code
    ,tmp_scan.unsign_dt
    ,tmp_scan.sign_dt
    ,tmp_scan.instore_early_storecode
    ,tmp_scan.instore_later_storecode
    ,tmp_scan.outstore_early_storecode
    ,tmp_scan.outstore_later_storecode
    ,tmp_scan.deliver_early_pistol_id
    ,tmp_scan.deliver_later_pistol_id
    ,tmp_order.phone_contact
    ,tmp_order.is_privacy
    ,tmp_order.order_source_code as order_source_code_order
    ,tmp_order.order_source_name as order_source_name_order
    ,tmp_abnormal.difficult_type_code
    ,tmp_abnormal.end_piece_type_code
    ,tmp_abnormal.intercept_type_code
    ,tmp_scan.end_site_arrival_later_time
    ,tmp_scan.dt
from  tmp_waybill_scan tmp_scan
left join tmp_waybill_detail tmp_waybill
on tmp_scan.waybill_no = tmp_waybill.waybill_no
left join tmp_abnormal_detail tmp_abnormal
on tmp_scan.waybill_no = tmp_abnormal.waybill_no
left join tmp_order_detail tmp_order
on tmp_scan.waybill_no = tmp_order.waybill_id
--where tmp_scan.dt is not null
--and dt >= date_add('{{ execution_date | cst_ds }}', -29)
distribute by dt, abs(hash(waybill_no)) % 60 ;
